Hello everyone.
In this lecture we will cover some of the basic mathematical functions in Excel.
But before that let us understand this small difference between formulas and functions in Excel.
Most people used interchangeably but there is a small difference between the two.
A formula is an expression which calculates the value of a cell.
It can be any expression.
What we learned in the last class what mathematical formulas so does is equal to A1 plus B when a function
is a predefined formula.
Functions allow you to use descriptive names to automatically apply formulas for you.
For example some average etc. functions we will see how to use them.
We use functions because they increase user productivity.
When working with Excel.
So I hope the difference is clear.
Anything that is calculated in a cell is a formula but functions are predefined formulas of exit with
descriptive names.
Now let's see some of the mathematical functions we use in Excel.
Let's just look at the data we have in this data.
We have scores of 5 student in 3 subjects maths science and English.
We want to find out the total marks scored by these student.
For this we can simply write the formula as earlier using the plus symbols or we can use the sum function.
Let's use the sum function.
This time we go to the cell and start typing is equal to assume start the bracket
within the bracket.
A function takes parameters on Richard operate for example for the sum function we need to give all
the numbers which we want to add.
There are two ways to enter these numbers.
One is to enter each cell individually separated by Obama.
Let's do it this way.
For this rule for cell comma.
Second cell comma the third to close the record.
Enter.
And this is the sum of these three numbers.
If you click the tail again in the formula bar you can see the function applied instead of using cell
reference.
You could write the value of the cell also like some ninety five eighty three eighty one.
That would give you the same result but as discussed in the last lecture using cell reference has some
benefit for the student too.
We will use the second method instead of entering each individual cell.
I'm going to select the whole series this time we start again which is equal to some
within bracket.
We use the most to select the entire range of cells that we want to add close the bracket and head into.
Let's compare the difference between these two formulas in the formula bar in the first formula.
You can see that the three cells are separated by commas in the second formula.
Only the end point of the cities are written and between them is a colon.
So this represents that everything between M6 and 06 is to be added.
Can you see the power of some function over the normal addition formula using plus symbols.
If I have to add 100 numbers I do not need to write the cell reference of hundreds cells just like the
entire cities within the same function.
Now for the third student I want you to notice another feature of exit when in a cell we type is equal
to exit.
I expect that we are going to enter a function and it automatically starts recommending functions as
we type the first few alphabets of the function name.
If you do not want the function do not bother.
Just continue typing Marty if you want is a district function prestige tab key
it will auto complete the function as well as it will start any bracket for you.
Lastly to calculate for other student we will simply drag the formula by going to the bottom right corner
and extending it to other student
using any function is similar to this does type the name of the function give the parameters in the
bracket and enter that's it.
Let's cover other mathematical functions quickly no.
Next we look at the main function which returns the minimum value from the input data.
So to get the minimum score in maths we go to this cell and start typing is equal to men.
Bob when we hit tab this function will be selected in the parameters.
We need to select the range of cells to these five.
What this code of student and Matt refloated record and it ended.
You can see that forty five is the minimum scored scored by any student in maths we can extend this
formula for science and English also.
So 53 is the minimum score inside and fifty five is the minimum score in English.
Next we look at the max function max function returns the maximum value from the input data so to get
maximum value of scored in maths we go to this L and rate is equal to Max.
If you want to select the second formula here you can use the arrow key to go to second and then press
tab.
No we need to give the series from which we have to select the maximum score.
You can see that ninety nine is the maximum marks in that we can extend it for science and English also
next is the average function.
This function returns the average of the mean value of the input numbers.
If you remember in the last lecture we calculated average using usual mathematical operators this time
we will de function so to find average marks of each student go to the cell
type is equal to average.
When Excel suggests average function you can hit the tab key
after this select the range of sales for which you want to calculate the average price.
Enter
drag to get the average for others to rent
once we have calculated the average marks.
We may want to assign ranks to students also as to which student scored the highest marks and whose
score at least to rank these students base is their average school.
Go to the next sale type is equal to rank average.
The first barometer is the number whose rank you want to find.
This will be the quote of the student.
The second parameter and this function is DCD from which rank is to be calculated.
So this will be the scores of all these student
the last barometer is how should the ranking be done whether highest should be ranked 1 or the lowest
should be ranked 1.
If you want high used to be like one such as the case here.
The third parameter will be zero for the other scenario.
It will be one.
So we enter 0
and press entered
you can see that the average score of student 1 makes the student 1 second in class
important thing to note errors.
If we drag this formula down the reference will range will change.
So so the reference range changes from B 5 to be 9 2 b6 to be 10 b6 to be done is not the correct reference
range in the last lecture.
We discussed how to maintain the reference range to lock the reference range.
We need to use dollar signs
to do this.
Go to the reference values and add dollar symbols.
There is a shortcut for adding dollar symbols also just click on DL on what you want to add.
Dollar symbols and press effort
dollar symbols will be added to board row and column name.
This will restrain reference values to change while dragging.
So now let's extend it for others student
now you can see that the reference still remains from P 5 to be name and student four is now at rank
1 and student 5 is a student with the least average marks next.
We move on to some product formula in this formula.
We import two or more series of numbers
and it returns these sum of product of individual elements of these two series of numbers.
For example if we want to assign weight age to the marks of mad science and English of these students
before calculating their ranks we have written the relative weight age of each object here.
Now I want to find the total marks as but with age that is I want to find the sum of ninety five into
two plus eighty three into one plus eighty one into one point three.
This will be the total marks for student 1
to do this.
We go to the cell and type is equal to some product
then we select the first series of numbers comma.
Select the second series of numbers and press enter
we get the value of these seventy eight point three.
This is calculated by adding ninety five into two plus eighty three into one plus eighty one into one
point three.
This can be dragged down but before dragging it remember to use dollar symbols for debate digits.
We can drag this down.
No.
And here we have the total marks of student as body weighted.
Next we look at line function around function is used to generate a random value between 0 and 1.
This is an amazing function.
It takes no input parameter.
It just generates a number between 0 and 1 randomly.
But why do we need random numbers random numbers can be used to simulate jostling rooms like throwing
a dice or tossing a coin.
For example say we want to randomly select a class monitor or a class representative.
Out of these 5 student I will generate one random number for each student.
Whatever gets the largest number becomes class right.
So we go to this cell and type is equal to Ardi and the
data.
You can see a random value generated for Student 1.
We can drag it down for all the student
we can see that student 5 has the maximum value.
And now student 5 can be selected as the class representative.
Next we move to Iran between function this will return a random value between the range that we have
provided.
So this function takes two values as input and generates a random number between these two numbers.
In our example four students six we randomly assigned marks for maths by going to this day and typing
is equal to right between
and giving the range as forty eight and hundred
this student has scored 56 marks in maths.
We can extend this to science and English also.
You can see that every time you change any sale the value of run and then between get refreshed.
So now this student has scored eighty eight in maths for the doing science and seventy 77 in English.
Let's see this one more time.
If I add any value to a new city and press enter these values a refreshed
basically doing any operation in the Excel sheet triggers a refreshing of these values to stop this
from happening.
We can use deep based as value technique.
So the thing is that these cells contain formulas whereas we are interested in the value already generated
by these formulas the formulas have already served their purpose.
So what we do is we copy the contents of these cells the content of the cell is a formula.
You can see that by pasting it in any other cell
you can see in the formula bar when I pasted it still contains the formula but we want to keep the value
and not the formula.
So we copy these cells and paste them here
as values.
This is the option for pasting as values.
This comes only when you have copied a formula.
It basically gives the option to copy paste a formula as a formula or any value calculated by the formula.
Now as I have pasted the values I have randomly generated values with me which won't refresh every time
I perform any operation.
There are several other mathematical formulas such as the ones in the sheet.
We encourage you to write these on your own.
You can also find a list of all the formulas in the Excel menu click on these formulas tab and select
matching trigonometry
where you can see the list of all the match related functions
that's all for this lecture.
See you in the next 1.